﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace MIT.DivHead
{
    public partial class DivHeadIntFilter : System.Web.UI.Page
    {
        String query1;
        String query3;
        String query4;
        String query5;
        String FullQueryString;
        string[] gpaarray = new string[100];
        string[] wxparray = new string[100];
        string[] alstreamarray = new string[100];
        string[] classarray = new string[100];
        string[] cstatusarray = new string[100];
        int gpaarray1 = 0;
        int wxparray1 = 0;
        int alstreamarray1 = 0;
        int cstatusarray1 = 0;
        int classarray1 = 0;
        string[] globalarray = new string[100];
        string[] globalarray1 = new string[100];
        string[] globalarray2 = new string[100];
        string[] globalarray3 = new string[100];

        int globalarraycounter1 = 0;
        int globalarraycounter2 = 0;
        int globalarraycounter3 = 0;

        String Req1;
        String Req2;
        String Req3;
        String NewsDate1;
        String NewsDate2;
        String NewsDate3;
        String NewsTitle1;
        String NewsTitle2;
        String NewsTitle3;
        String NewsDetails1;
        String NewsDetails2;
        String NewsDetails3;

        protected void Page_Load(object sender, EventArgs e)
        {
            String Name = (String)Session["LoggingInfo"];
            String Position = (String)Session["LoggingPosition"];
            String username = (String)Session["LoggingInfoUsername"];
            LabelDivAdminName.Text = Name;
            LabelDivAdminPosition.Text = "(" + Position + ")";

            ImageProfile.ImageUrl = "../ProfilePicHandler.ashx?uname=" + username;

            Session["CalLoggingInfo"] = Name;
            Session["CalLoggingPosition"] = Position;
            Session["CalLoggingInfoUsername"] = username;

            ///////////////////Notification Number//////////////////////////////////////////////////
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand ReqMsgNo = new SqlCommand("spMsgCounter", con);
                ReqMsgNo.CommandType = CommandType.StoredProcedure;

                SqlParameter ReqName3 = new SqlParameter("@username", username);
                ReqMsgNo.Parameters.Add(ReqName3);



                con.Open();
                SqlDataReader dr;

                dr = ReqMsgNo.ExecuteReader();

                if (dr.Read())
                {
                    Req1 = Convert.ToString(dr["REQUEST1"]);
                    Req2 = Convert.ToString(dr["REQUEST2"]);
                    Req3 = Convert.ToString(dr["REQUEST3"]);
                }

            }
            if (Req1 == "")
            {
                LabelMsgNo.Text = "";

            }
            else if (Req2 == "")
            {
                LabelMsgNo.Text = "1";
            }
            else if (Req3 == "")
            {
                LabelMsgNo.Text = "2";
            }
            else
            {
                LabelMsgNo.Text = "3";
            }


            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand GetNews = new SqlCommand("spGetNews1", con);
                GetNews.CommandType = CommandType.StoredProcedure;

                SqlParameter News1 = new SqlParameter("@News1", 1);
                GetNews.Parameters.Add(News1);



                con.Open();
                SqlDataReader dr;

                dr = GetNews.ExecuteReader();

                if (dr.Read())
                {
                    NewsDate1 = Convert.ToString(dr["LiveDate"]);
                    NewsTitle1 = Convert.ToString(dr["Title"]);
                    NewsDetails1 = Convert.ToString(dr["Details"]);

                }

            }

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand GetNews = new SqlCommand("spGetNews2", con);
                GetNews.CommandType = CommandType.StoredProcedure;

                SqlParameter News2 = new SqlParameter("@News2", 2);
                GetNews.Parameters.Add(News2);



                con.Open();
                SqlDataReader dr;

                dr = GetNews.ExecuteReader();

                if (dr.Read())
                {
                    NewsDate2 = Convert.ToString(dr["LiveDate"]);
                    NewsTitle2 = Convert.ToString(dr["Title"]);
                    NewsDetails2 = Convert.ToString(dr["Details"]);

                }

            }

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand GetNews = new SqlCommand("spGetNews3", con);
                GetNews.CommandType = CommandType.StoredProcedure;

                SqlParameter News3 = new SqlParameter("@News3", 3);
                GetNews.Parameters.Add(News3);



                con.Open();
                SqlDataReader dr;

                dr = GetNews.ExecuteReader();

                if (dr.Read())
                {
                    NewsDate3 = Convert.ToString(dr["LiveDate"]);
                    NewsTitle3 = Convert.ToString(dr["Title"]);
                    NewsDetails3 = Convert.ToString(dr["Details"]);
                }

            }



            LabelDetails1.Text = NewsDetails1;
            LabelDetails2.Text = NewsDetails2;
            LabelDetails3.Text = NewsDetails3;

            LabelNews1.Text = NewsTitle1;
            LabelNews2.Text = NewsTitle2;
            LabelNews3.Text = NewsTitle3;

            LabelDate1.Text = NewsDate1;
            LabelDate2.Text = NewsDate2;
            LabelDate3.Text = NewsDate3;




        }

        protected void Button1_Click(object sender, EventArgs e)
        {


            if (CheckBox1.Checked)
            {

                query1 = TextBoxFGPA.Text;
                String ConString = ConfigurationManager.ConnectionStrings["DBCSFINAL"].ConnectionString;
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    connection.Open();
                    string GetID = "select CV_NO from CV where GPA >= '" + query1 + "'";
                    SqlCommand cmd4 = new SqlCommand(GetID, connection);
                    using (SqlDataReader rdr = cmd4.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            gpaarray[gpaarray1] = rdr["CV_NO"].ToString();
                            gpaarray1++;

                        }
                    }
                    connection.Close();
                }

            }
            if (CheckBox3.Checked)
            {
                query3 = DropDownListFALStream.SelectedValue;
                String ConString = ConfigurationManager.ConnectionStrings["DBCSFINAL"].ConnectionString;
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    connection.Open();
                    string GetID = "select CV_NO from CV where ALSTREAM = '" + query3 + "'";
                    SqlCommand cmd4 = new SqlCommand(GetID, connection);
                    using (SqlDataReader rdr = cmd4.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            alstreamarray[alstreamarray1] = rdr["CV_NO"].ToString();
                            alstreamarray1++;
                        }
                    }
                    connection.Close();
                }

            }

            if (CheckBox4.Checked)
            {
                query4 = DropDownFListClass.SelectedValue;
                String ConString = ConfigurationManager.ConnectionStrings["DBCSFINAL"].ConnectionString;
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    connection.Open();
                    string GetID = "select CV_NO from CV where CLASS = '" + query4 + "'";
                    SqlCommand cmd4 = new SqlCommand(GetID, connection);
                    using (SqlDataReader rdr = cmd4.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            classarray[classarray1] = rdr["CV_NO"].ToString();
                            classarray1++;
                        }
                    }
                    connection.Close();
                }

            }

            if (CheckBox5.Checked)
            {
                query5 = DropDownListCStatus.SelectedValue;
                String ConString = ConfigurationManager.ConnectionStrings["DBCSFINAL"].ConnectionString;
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    connection.Open();
                    string GetID = "select CV_NO from CV where CIVILSTATUS = '" + query5 + "'";
                    SqlCommand cmd4 = new SqlCommand(GetID, connection);
                    using (SqlDataReader rdr = cmd4.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            cstatusarray[cstatusarray1] = rdr["CV_NO"].ToString();
                            cstatusarray1++;
                        }
                    }
                    connection.Close();
                }

            }

            int x = 0;
            int y = 0;
            int z = 0;
            int globalarraycounter = 0;
            if (gpaarray[0] != null && wxparray[0] != null)
            {
                for (int n = 0; n < gpaarray1; n++)
                {
                    for (int j = 0; j < wxparray1; j++)
                    {
                        if (gpaarray[n] == wxparray[j])
                        {
                            globalarray[x] = gpaarray[n];
                            x++;
                        }

                    }

                }

            }
            else if (gpaarray[0] != null)
            {
                gpaarray.CopyTo(globalarray, 0);
            }

            else if (wxparray[0] != null)
            {
                wxparray.CopyTo(globalarray, 0);
            }

            for (int i = 0; i < 100; i++)
            {
                if (globalarray[i] != null)
                {
                    globalarraycounter1++;
                }

            }






            if (alstreamarray[0] != null && globalarray[0] != null)
            {
                for (int n = 0; n < alstreamarray1; n++)
                {
                    for (int j = 0; j < globalarraycounter1; j++)
                    {
                        if (alstreamarray[n] == globalarray[j])
                        {
                            globalarray1[y] = alstreamarray[n];
                            y++;
                        }

                    }

                }
            }

            else if (alstreamarray[0] != null)
            {
                alstreamarray.CopyTo(globalarray1, 0);
            }

            else if (globalarray[0] != null)
            {
                globalarray.CopyTo(globalarray1, 0);
            }

            for (int i = 0; i < 100; i++)
            {
                if (globalarray1[i] != null)
                {
                    globalarraycounter2++;
                }

            }

            if (classarray[0] != null && globalarray1[0] != null)
            {
                for (int n = 0; n < classarray1; n++)
                {
                    for (int j = 0; j < globalarraycounter2; j++)
                    {
                        if (classarray[n] == globalarray1[j])
                        {
                            globalarray2[z] = classarray[n];
                            z++;
                        }

                    }

                }
            }

            else if (classarray[0] != null)
            {
                classarray.CopyTo(globalarray2, 0);
            }

            else if (globalarray1[0] != null)
            {
                globalarray1.CopyTo(globalarray2, 0);
            }

            for (int i = 0; i < 100; i++)
            {
                if (globalarray2[i] != null)
                {
                    globalarraycounter3++;
                }

            }




            if (cstatusarray[0] != null && globalarray2[0] != null)
            {
                for (int n = 0; n < cstatusarray1; n++)
                {
                    for (int j = 0; j < globalarraycounter3; j++)
                    {
                        if (cstatusarray[n] == globalarray2[j])
                        {
                            globalarray3[z] = cstatusarray[n];
                            z++;
                        }

                    }

                }
            }

            else if (cstatusarray[0] != null)
            {
                cstatusarray.CopyTo(globalarray3, 0);
            }

            else if (globalarray2[0] != null)
            {
                globalarray2.CopyTo(globalarray3, 0);
            }




            for (int i = 0; i < 100; i++)
            {
                if (globalarray3[i] != null)
                {
                    globalarraycounter++;
                }

            }

            String CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {

                for (int i = 0; i < globalarraycounter; i++)
                {
                    SqlCommand cmd = new SqlCommand("spfiltercvno", con);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@CV_NO", globalarray3[i]);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
            String CS1 = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS1))
            {
                con.Open();
                FullQueryString = "select CV.CV_NO,CV.VACANCY,CV.FIRSTNAME,CV.MIDDLENAME,CV.LASTNAME,CV.GENDER,CV.CIVILSTATUS,CV.RESIADDRESS,CV.CONTACTNO,CV.GPA,CV.CLASS,CV.ALSTREAM,CV.WORKEXPERIENCE from CV JOIN FILTERTABLE ON CV.CV_NO=FILTERTABLE.CV_NO";
                SqlDataAdapter da = new SqlDataAdapter(FullQueryString, con);
                DataSet ds = new DataSet();
                da.Fill(ds);

                GridView1.DataSource = ds;
                GridView1.DataBind();
                con.Close();
                string constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                using (SqlConnection con1 = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = "SELECT CV.CV_NO, CV.FIRSTNAME from CV JOIN FILTERTABLE ON CV.CV_NO=FILTERTABLE.CV_NO";
                        cmd.Connection = con1;
                        con1.Open();
                        GridView2.DataSource = cmd.ExecuteReader();
                        GridView2.DataBind();
                        con1.Close();


                    }
                }
            }

            String CS2 = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection deleteconnection = new SqlConnection(CS2))
            {
                SqlCommand cmd = new SqlCommand("spDelfiltertable", deleteconnection);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                deleteconnection.Open();
                cmd.ExecuteNonQuery();
            }





        }
        protected void ButtonLogout_Click(object sender, EventArgs e)
        {
            Response.Redirect("../Login/SignIn.aspx");
        }

        protected void View(object sender, EventArgs e)
        {
            int id = int.Parse((sender as LinkButton).CommandArgument);
            string embed = "<object data=\"{0}{1}\" type=\"application/pdf\" width=\"800px\" height=\"600px\">";
            embed += "If you are unable to view file, you can download from <a href = \"{0}{1}&download=1\">here</a>";
            embed += " or download <a target = \"_blank\" href = \"http://get.adobe.com/reader/\">Adobe PDF Reader</a> to view the file.";
            embed += "</object>";
            Label1.Text = string.Format(embed, ResolveUrl("~/FileCS.ashx?CV_NO="), id);
        }


        protected void ButtonSelCV_Click(object sender, EventArgs e)
        {
            //DataTable dt = new DataTable();
            //foreach (GridViewRow item in GridView1.Rows)
            //{
            //    if ((item.Cells[0].FindControl("cbSelect") as CheckBox).Checked)
            //    {
            //        DataRow dr = dt.NewRow();
            //        dr["FIRSTNAME"] = item.Cells[2].Text;
            //        dr["LASTNAME"] = item.Cells[4].Text;
            //        dt.Rows.Add(dr);


            //    }
            //}
            //GridView1.DataSource = dt;
            //GridView1.DataBind();

            // StringBuilder object
            StringBuilder str = new StringBuilder();

            // Select the checkboxes from the GridView control
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridViewRow row = GridView1.Rows[i];
                bool isChecked = ((CheckBox)row.FindControl("cbSelect")).Checked;

                if (isChecked)
                {
                    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(CS))
                    {
                        SqlCommand cmd = new SqlCommand("spSelectedCV", con);
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;


                        int a = Convert.ToInt32(GridView1.Rows[i].Cells[1].Text);
                        cmd.Parameters.AddWithValue("@CV_NO", a);

                        con.Open();
                        cmd.ExecuteNonQuery();
                        // Column 2 is the name column
                        //str.Append(GridView1.Rows[i].Cells[1].Text);
                    }
                }
            }

            // prints out the result
            //Response.Write(str.ToString());
        }

    }
}